package toubiao.utils.excel;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;







import org.plutext.jaxb.xslfo.ForcePageCountType;

import utils.UtilCollection;
import utils.UtilController;

public class ExcelFactory {

	static public Font getCommonHeadFont(Workbook workbook){
		Font headFont =workbook.createFont();
        headFont.setFontHeightInPoints((short) 10); //字体高度
        headFont.setColor(XSSFFont.DEFAULT_FONT_COLOR); //字体颜色
        headFont.setFontName("宋体"); //字体
        headFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); //宽度
        headFont.setItalic(false); //是否使用斜体
//      font.setStrikeout(true); //是否使用划线
        return headFont;
	}

	static public Font getCommonBodyFont(Workbook workbook){
		Font font =workbook.createFont();
        font.setFontHeightInPoints((short) 10); //字体高度
        font.setColor(XSSFFont.DEFAULT_FONT_COLOR); //字体颜色
        font.setFontName("宋体"); //字体
        font.setBoldweight(XSSFFont.DEFAULT_FONT_SIZE); //宽度
        font.setItalic(false); //是否使用斜体
//      font.setStrikeout(true); //是否使用划线
        return font;
	}
	
	static public CellStyle getCommonHeadCellStyle(Workbook workbook){
		CellStyle headCellStyle =workbook.createCellStyle();
		Font headFont=getCommonHeadFont(workbook);
        headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); 
        headCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
        headCellStyle.setFont(headFont);
        headCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
        headCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        headCellStyle.setWrapText(true);
        headCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        headCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        return headCellStyle;
	}
	
	static public CellStyle getCommonBodyCellStyle(Workbook workbook){
		Font font=getCommonBodyFont(workbook);
		CellStyle cellStyle =workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        return cellStyle;
	}
	
	static public CellStyle getCommonDateCellStyle(Workbook workbook){
		Font font=getCommonBodyFont(workbook);
		String fomatDateString="yyyy/M/d";
		CellStyle cellStyle=createDateForamtStyle(workbook,fomatDateString);
        cellStyle.setFont(font);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
      
        return cellStyle;
	}
	
	/**
	 * 生成 elsx文件,默认包含一个name=sheet 的worksheet
	 * @return
	 */
	static public Workbook createWorkbook(){
		Workbook workbook=new XSSFWorkbook();
		workbook.createSheet("sheet");
		return workbook;
	}
	
	/**
	 * @param sheet
	 * @param map Map<Integer,Map<Integer, Object>> 其中的<行号:map<列号，数据>>
	 * @param cellStyle
	 */
	static public void fillSheet(Sheet sheet,Map<Integer,Map<Integer, Object>> map,CellStyle cellStyle){
		if(map==null || map.size()==0){
			return;
		}
		for(Entry<Integer, Map<Integer, Object>> rowEntry:map.entrySet()){
			if(rowEntry==null){
				continue;
			}
			Integer rowNumber=rowEntry.getKey();
			Map<Integer, Object> rowMap=rowEntry.getValue();
			if(rowNumber==null || rowMap==null || rowMap.size()==0){
				continue;
			}
			Row row=sheet.createRow(rowNumber);
			for(Entry<Integer, Object> entry:rowMap.entrySet()){
				Integer columnNumber=entry.getKey();
				Object object=entry.getValue();
				if(columnNumber==null ){
					continue;
				}
				Cell cell=row.createCell(columnNumber);
				cell.setCellStyle(cellStyle);
				if(object==null){
					continue;
				}
				setCellValueByObject(cell, object);
			}
		}
	}
	
	
	
	    /**
	    * @Title: setColumnWith
	    * @Description: 设置sheet 的列宽度
	    * @param @param sheet 
	    * @param @param widthMap    Map<Integer, Integer>: <列号,宽度>,如果宽度未空或0，设置为自动宽度
	    * @return void    
	    * @throws
	    */
	static public void setColumnWith(Sheet sheet,Map<Integer, Integer> widthMap){
		if(sheet==null || widthMap==null || widthMap.size()==0){
			return ;
		}
		for(Map.Entry<Integer, Integer> entry:widthMap.entrySet()){
			Integer column=entry.getKey();
			Integer width=entry.getValue();
			if(column==null){
				continue;
			}
			if(width==null || width==0){
				sheet.autoSizeColumn(column);
			}else{
				sheet.setColumnWidth(column, width);
			}
		}
	}
	
	
	
	    /**
	    * @Title: setCellValueByObject
	    * @Description: 根据object 的类型设置 cell 的值
	    * @param @param cell
	    * @param @param object    
	    * @return void    
	    * @throws
	    */
	static public void setCellValueByObject(Cell cell,Object object){
		//System.out.println(object.getClass().getSimpleName()+" = "+object);
		Workbook workbook=cell.getSheet().getWorkbook();
		if(object instanceof String){
			cell.setCellValue(object.toString());
			return;
		}
		if(object instanceof Number){
			//System.out.println(object);
			cell.setCellValue(Double.parseDouble(object+""));
			//cell.setCellValue((Double)object);
			//cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			return;
		}
		if(object instanceof Date){
			cell.setCellValue((Date)object);
			CellStyle cellStyle=getCommonDateCellStyle(workbook);
			cell.setCellStyle(cellStyle);
			return;
		}
		if(object instanceof Calendar){
			
			cell.setCellValue((Calendar)object);
			
			return;
		}
		if(object instanceof Boolean){
			cell.setCellValue((Boolean)object);
			return;
		}
		cell.setCellValue(object.toString());
	}
	
	/**
	 * @param workbook
	 * @param dateFormat like:"yyyy-MM-dd";"yyyy/M/d"
	 * @return
	 */
	public static CellStyle createDateForamtStyle(Workbook workbook,String dateFormat) {
		CreationHelper createHelper = workbook.getCreationHelper();  
	    CellStyle cellStyle = workbook.createCellStyle();  
	    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));  
	    return cellStyle;
	}
}
